package service

import (
	"strings"

	"agentdesign/models"
)

// ListTemplates returns catalog templates with their associated tags.
func (s *ApiService) ListTemplates() ([]models.TemplateWithTags, error) {
	rows := []struct {
		ID             int    `db:"id"`
		Title          string `db:"title"`
		Description    string `db:"description"`
		ComplexityHint string `db:"complexity_hint"`
		Tags           string `db:"tags"`
	}{}

	err := s.db.Select(&rows, `
		SELECT
			t.id,
			t.title,
			COALESCE(t.description, '') AS description,
			COALESCE(t.complexity_hint, '') AS complexity_hint,
			COALESCE(GROUP_CONCAT(tt.name ORDER BY tt.name SEPARATOR ','), '') AS tags
		FROM templates t
		LEFT JOIN template_template_tags ttt ON t.id = ttt.template_id
		LEFT JOIN template_tags tt ON ttt.tag_id = tt.id
		GROUP BY t.id, t.title, t.description, t.complexity_hint
		ORDER BY t.id ASC`)
	if err != nil {
		return nil, err
	}

	templates := make([]models.TemplateWithTags, 0, len(rows))
	for _, row := range rows {
		tags := []string{}
		if row.Tags != "" {
			for tag := range strings.SplitSeq(row.Tags, ",") {
				trimmed := strings.TrimSpace(tag)
				if trimmed != "" {
					tags = append(tags, trimmed)
				}
			}
		}

		templates = append(templates, models.TemplateWithTags{
			Template: models.Template{
				ID:             row.ID,
				Title:          row.Title,
				Description:    row.Description,
				ComplexityHint: row.ComplexityHint,
			},
			Tags: tags,
		})
	}

	return templates, nil
}

// ListRecentHistory returns the most recent project history entries.
func (s *ApiService) ListRecentHistory(limit int) ([]models.ProjectHistoryEntry, error) {
	if limit <= 0 {
		limit = 10
	}

	history := []models.ProjectHistoryEntry{}
	err := s.db.Select(&history, `
		SELECT
			h.id,
			h.project_id,
			COALESCE(p.name, '') AS project_name,
			COALESCE(h.summary, '') AS summary,
			COALESCE(h.device, '') AS device,
			COALESCE(h.page_count, 0) AS page_count,
			COALESCE(h.status, '') AS status,
			h.occurred_at
		FROM project_history h
		LEFT JOIN projects p ON h.project_id = p.id
		ORDER BY h.occurred_at DESC
		LIMIT ?`, limit)

	return history, err
}

// ListConfigurationPresets loads preset groups with nested options.
func (s *ApiService) ListConfigurationPresets() ([]models.ConfigurationGroup, error) {
	groups := []models.ConfigurationGroup{}
	queryGroups := `
		SELECT
			id,
			` + "`key`" + `,
			title,
			COALESCE(subtitle, '') AS subtitle,
			COALESCE(icon, '') AS icon
		FROM configuration_groups
		ORDER BY id ASC`

	err := s.db.Select(&groups, queryGroups)
	if err != nil {
		return nil, err
	}

	options := []models.ConfigurationOption{}
	queryOptions := `
		SELECT
			id,
			group_id,
			` + "`key`" + `,
			label,
			COALESCE(description, '') AS description
		FROM configuration_options
		ORDER BY group_id ASC, sort_order ASC, id ASC`

	err = s.db.Select(&options, queryOptions)
	if err != nil {
		return nil, err
	}

	optionMap := make(map[int][]models.ConfigurationOption)
	for _, option := range options {
		optionMap[option.GroupID] = append(optionMap[option.GroupID], option)
	}

	for i := range groups {
		groups[i].Options = optionMap[groups[i].ID]
	}

	return groups, nil
}
